mysql入门学习笔记

最近在软件测试培训班内学习嘛 刚好在教这方面的东西 就把现有的笔记复制上来 就当云备份了 但毕竟不是专业的 应付普通的增删改查完全够用了 就没学数据库的设计规范

是按照 数据结构 来组织 存储 和管理数据的仓库

是指数据库系统中对数据进行管理的软件系统

1关系型数据库（SQL server Oracle MySQL DB2 Access sybase）

2非关系形数据库（nosql mongodb redis HBase）

建立在关系模型上的数据库

借助集合 代数 等数学概念 和 方法来处理数据库中的数据

进入安装路径 找到my.ini文件 记事本打开 查看端口号 默认为3306

services.msc 手动启动或者关闭mysql服务

cmd关闭 net stop mysql

cmd启动 net start mysql

MySQL -V

-u 后接用户名

-p 后接密码

-V 输出版本号

-h 主机地址

\q quit exit ctrl+c 退出

mysql -uroot -p -h127.-0.0 .1

mysqladmin -u用户名 -p密码 password 新密码

show databases;

use 数据库名;

select database();

select version();或者status

select now();

select user();

select password('用户名'); 密码加密

create database if not exists 库名 DEFAULT character set 字符集;

alert database 数据库名 character set 字符集;

使用库 之后 show variables like ‘character%’

show create database 数据库;

drop database [if exists] 库名;

if exitsts 是否存在 存在就删除 不存在也不会直接报错 有提示

数据类型是指

数据列 存储过程参数 表达式 和局部变量的数据特征 它决定了数据的存储格式 代表了不同的信息类型

TINYINT 有符号-128--127 无符号 0-255

SMALLINT -32768 32767 无符号0-65535

MEDIUMINT

INT

BIGINT

FLOAT(M,D)

DOUBLE(M,D)

DECIMAL(M,D)

M是精度=整数+小数位

D是标度（小数点后位数）

YEAR YYYY

TIME HH:MM:SS

DATE YYYY-MM-DD

DATETIME YYYY-MM-DD HH:MM:SS

TIMESTAMP YYYY-MM-DD HH:MM:SS

char(M)

varchar

tinytext

text

mediumtext

longtext

ENUM(' ',' ')

SET(' ',' ')

是数据库最重要的组成部分之一 是其他对象的基础

tinyint smallint mediumint int bigint

float double decimal

year time date datetime

char varchar text enum(' ',' ')枚举型

create table 表名(表头 数据类型,表头2 数据类型,表头3 数据类型);

show tables from 库名字;

show columns from 表名;

describe 表名;

desc 表名;

show create table 表名;

show create table 表名字;

alter table 表名 add 表头 数据类型 [约束条件 first|after 表头];

alter table 表名 change 旧列名 新列名 数据类型; 可以用这改数据类型 新旧列名一样

alter t able 表名 modify 列名 数据类型;

alter table 表名 modify 表头 数据类型 first|after 表头2;

alter table 表名 drop 表头;

alter table 旧表名 rename to 新表名;

DROP TABLE [IF EXISTS] 表1,表2,表3 删除没有被关联的表 如果多表关联 得解除外键关联关系之后才能删表

表承载数据量很大时 得去考虑优化 进行表分区

将一张表的数据分成多个区块 区块可以放在同一个磁盘 也可以放在多个磁盘 但是数据必须在一个表中

前提是数据库得支持

show plugins;

分区有两种方式 水平和垂直

partition by 类型（字段）

指定某列范围值来进行分区

使用 values less than来定义分区

partition by range(表头)(

partition 区块名 value less than(数值),可同时分多个区

);

select * from 表名 partition(区块名);

约束是一种限制 对表的行或列的数据做出限制 来确保表的数据的完整性 唯一性

NOT NULL

添加数据时设置了非空字段的值不能为空 否则会报错

PRIMARY KEY

要求主键列的数据唯一 并且不能为空 唯一标识表中的一条记录

分为单字段主键 和 多字段联合主键

UNIQUE

唯一约束 保证该字段值唯一，但可为空 主键和唯一键的区别： ①都可以保证唯一性②主键不允许为空，唯一允许为空（只能插入一个null）③一个表中至多一个主键，唯一键可以有多个④都可以组合使用（不推荐）

DEFAULT

默认约束 用于保证该字段有默认值

FOREIGN KEY

①要求在从表设置外键关系

②从表的外键列类型和主表的关联列类型要求一致或兼容，名称无要求

③主表的关联列必须是一个key（一般是主键）

④要求插入数据时，先插入主表，再插入从表，删除数据时，先删除从表，再删除主表。

修改表时添加约束：

[CONSTRAINT ] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES 主键列1 [，主键列2，…]

1.create table 表名字(

表头 数据类型 约束

);

constraint 约束名 （primary key） 表头;

alter table 表名 modify 表头 数据类型;

alter table 表名 DROP primary key;

parmary key (表头1,表头2,表头3);

create table 表名(

表头 数据类型,

表头2 数据类型,

表头3 数据类型,

parmary key (表头1,表头2,表头3);

);

alter table 表名 modify column 列名 数据类型 not null

alter table 表名 modify column 列名 数据类型 default 默认值

alter table 表名 modify column 列名 数据类型 primary key alter table 表名 add primary key（列名）

alter table 表名 modify column 列名 类型 unique alter table 表名 add unique（列名）

alter table 子表表名 add foreign key（列名） references 主表表名（列名）

alter table 表名 modify column 列名 类型 null

alter table 表名 modify column 列名 类型

alter table 表名 drop primary key

alter table 表名 drop index 键名

alter table 表名 drop foreign key 键名

insert into 表名（表头1）values(字段1,字段2)

insert into 表名 values(字段1,字段2)；

insert into 表名 select * from 表名 where 条件;

auto_increment;创表时直接当成约束使用

alter table 表名 modify 表头 数据类型 auto_increment

设置自增初始值

alter table 表名 auto_increment=初始值

update 表名 set column_name1 = value1, column_name2 = value2 where 条件

delete from 表名

truncate [table] 表名

区别

delete后面可以带条件 truncate 不能带条件

delete速度低 效率慢 truncate 速度快 效率高

select * from table

select 表头1,表头2 from table

select distinct 表头 from table

select * from table where 表头 is null

group by 列名字 having 条件表达式

select * from table group by 表头 having 条件

group分组 having 过滤

先分组再过滤

ORDER BY column ASC/DESC;默认升序

select * from table limit 数字;

asc升序 默认 desc降序

limit 指定条(默认第0条) , 数量

limit 0,3

limit 数量 offset 指定条开始;

limit 3 offset 0

+，-，*，/，%（取余）

< , > ,>= , 18 or balance=0 日期时间函数 获取日期函数 CURDATE(); current_date(); 获取时间函数 curtime(); current_time();

默认输出为hh:mm:ss 加数字为hhmmss

输出格式为yyyy-mm-dd-ss

date参数yyyy-mm-dd

time1-time2

%b 月份 英文缩写

%c 月份 单数字

%m 月份 双数字

%M 月份 全英文

%d 日期 双数字

%e 日期 单数字

%Y 年份 4位数

%y 年份 2位数

聚合函数 分组函数

加密后位32位十六进制数字的二进制字符串形式返回 参数为null则返回null

password(str)从原明文密码str计算并返回加密后的密码字符

set password = password('新密码')

select user,password from mysql.user

子查询是指嵌套在其他sql语句中的查询语句

any some同义词 表示满足内层子查询中的任何一个条件

all表示需要满足所有内层查询的条件

exists 如果存在

[inner] join

例 select 表头 from table [inner] join table2 on 表1.表头 = 表2.表头

left right full

例 select 表头 from table [left|right| full] join table2 on 表1.表头 = 表2.表头

例 select 表头 from table [inner] join table2 on 表1.表头 = 表2.表头 [inner] join table3 on 表1.表头 = 表3.表头

update 表1 inner join 表2 on 表1.id=表2.id set 条件 where 表1.表头=‘’；

create table table2 as select * from table1;

delete t1,t2 from table inner table2 on table.id=table2.id where 条件

create function 函数名(传入数据 数据类型)

returns 数据类型

return DATE_FORMAT(传入数据,'%Y-%m');

DELIMITER //

create function 函数名(传入数据 数据类型)

returns 数据类型

BEGIN

return DATE_FORMAT(传入数据,'%Y-%m');

END //

DELIMITER;

create function 函数名()

returns 数据类型

return DATE_FORMAT(传入数据,'%Y-%m');

create function show_name_store(bid int) returns varchar(50) return select concat_ws('---',book_name,store) from bookinfo where bid=book_id

drop funciton [if exist] function_name

DECLARE VAR_NAME date_type[]

例如

declare num int default 10;

1 set VAR_NAME=expr [,var_name = expr]

例如 set num = 100

2 select col_name[,....] into var_name[,.....] table_expr

例如 select store INTO num From bookinfo；

在bengin end之间

格式

if 条件 then

​ 触发后执行事件;

elseif 条件 then

​ 触发后执行事件;

else

​ 触发后执行事件;

end if;

return

1 CASE CASE_EXPR

​ WHEN when_value THEN statement_list

ELSE 条件语句

END CASE

case while......do....... loop repeat

存储过程是 sql语句 和 流程控制语句 的预编译集合 并以一个名称存储 并作为一个单元进行处理

create procedure 存储过程名称([ IN|OUT|INOUT ] 传入参数 类型)

routine_body [过程体]

in 必须在调用存储过程时被指定

out该参数的值可以被存储过程改变 并且可以返回

inout 表示该参数调用时指定并且可以被改变和返回

call 存储过程名称(); 调用存储过程

过程体是合法sql语句

过程体可以是任意sql语句

过程体如果为复合结构就使用begin......end语句

复合结构可以包含声明，流程控制语句

mysqldump -u user -p password -h localhost 数据库名 库内表名>指定路径

mysqldump -u user -p password -h localhost --databases 数据库名 数据库名>指定路径

mysqldump -u user -p password -h localhost --all -databases >指定路径

mysql -uroot -proot 表名路径